DROP TABLE IF EXISTS jms_ods.yl_tms_shipment;   
CREATE EXTERNAL TABLE jms_ods.yl_tms_shipment(   
 ID bigint comment "id",  --NUMBER(20)
 SHIPMENT_NO string comment "任务单号",  --NVARCHAR2(30)
 DISTRIBUTION_TYPE tinyint comment "集散货类型(0干线、1集货交件、2散货派件)",  --NUMBER(2)
 BUSINESS_ATTRIBUTE bigint comment "业务属性 1:正班 2:加班",  --NUMBER(11)
 SHIPMENT_NAME string comment "任务名称",  --NVARCHAR2(500)
 SHIFTS bigint comment "班次",  --NUMBER(11)
 VEHICLELINE_TYPE bigint comment "运营模式",  --NUMBER(11)
 SHIPMENT_STATE bigint comment "任务状态：待调度0 已调度1  在途3 已完成4 已作废5",  --NUMBER(11)
 LINE_ID bigint comment "车线ID",  --NUMBER(11)
 LINE_CODE string comment "车线CODE",  --NVARCHAR2(30)
 LINE_NAME string comment "车线NAME",  --NVARCHAR2(100)
 START_ID bigint comment "始发网点ID",  --NUMBER(11)
 START_CODE string comment "始发网点CODE",  --NVARCHAR2(20)
 START_NAME string comment "始发网点NAME",  --NVARCHAR2(50)
 START_FINANCIAL_CENTER_ID bigint comment "始发代理区ID",  --NUMBER(11)
 START_FINANCIAL_CENTER_CODE string comment "始发代理区CODE",  --NVARCHAR2(30)
 START_FINANCIAL_CENTER_DESC string comment "始发代理区名称",  --NVARCHAR2(60)
 END_ID bigint comment "目的网点ID",  --NUMBER(11)
 END_CODE string comment "目的网点CODE",  --NVARCHAR2(20)
 END_NAME string comment "目的网点NAME",  --NVARCHAR2(50)
 END_FINANCIAL_CENTER_ID bigint comment "目的代理区ID",  --NUMBER(11)
 END_FINANCIAL_CENTER_CODE string comment "目的代理区CODE",  --NVARCHAR2(30)
 END_FINANCIAL_CENTER_DESC string comment "目的代理区Name",  --NVARCHAR2(60)
 CNT bigint comment "途径地数量",  --NUMBER(11)
 MILEAGE double comment "计划系统里程(KM)",  --NUMBER
 TOTAL_RUNTIME bigint comment "计划总运行时间(MIN)",  --NUMBER(11)
 STANDARD_SPEED double comment "计划标准时速",  --NUMBER
 ACTUAL_MILEAGE double comment "实际系统里程(KM)",  --NUMBER
 ACTUAL_TOTAL_RUNTIME bigint comment "实际总运行时间(MIN)",  --NUMBER(11)
 ACTUAL_STANDARD_SPEED double comment "实际时速",  --NUMBER
 PLANNED_DEPARTURE_TIME timestamp comment "规划发车时间",  --DATE
 PLANNED_ARRIVAL_TIME timestamp comment "规划到车时间",  --DATE
 ACTUAL_DEPARTURE_TIME timestamp comment "实际发车时间",  --DATE
 ACTUAL_ARRIVAL_TIME timestamp comment "实际到车时间",  --DATE
 VEHICLE_GROUPID bigint comment "规划车型ID",  --NUMBER(11)
 VEHICLE_TYPEGROUP string comment "规划车型",  --NVARCHAR2(50)
 ACTUAL_VEHICLE_GROUPID bigint comment "实际车型ID",  --NUMBER(11)
 ACTUAL_VEHICLE_TYPEGROUP string comment "实际车型",  --NVARCHAR2(50)
 PLATE_NUMBER string comment "车牌号",  --NVARCHAR2(30)
 LOAD_WEIGHT double comment "核定载重(KG)",  --NUMBER
 VEHICLE_VOLUME double comment "核定体积（M3）",  --NUMBER
 DRIVER_NAME string comment "司机名称",  --NVARCHAR2(50)
 DRIVER_CONTACT string comment "司机联系方式",  --NVARCHAR2(20)
 VEHICLETYPE_ID string comment "车型ID",  --NVARCHAR2(50)
 VEHICLETYPE_NAME string comment "车型名称",  --NVARCHAR2(50)
 VEHICLE_CLASS_ID string comment "车型ID",  --NVARCHAR2(10)
 VEHICLE_CLASS_NAME string comment "车型名称",  --NVARCHAR2(10)
 TRANSPORT_TYPE string comment "运力类型",  --NVARCHAR2(30)
 OPERATION_MODEL bigint comment "运行模式",  --NUMBER(11)
 TRAILER_NUMBER string comment "挂车号",  --NVARCHAR2(30)
 QUOTATION_MODEL bigint comment "报价模式 1 加班价 2 正班价",  --NUMBER(11)
 LOAD_TIME timestamp comment "装车时间",  --DATE
 LOCK_TIME timestamp comment "锁车时间",  --DATE
 CLOCK_TIME timestamp comment "中心打卡时间",  --DATE
 UNLOCK_TIME timestamp comment "解锁时间",  --DATE
 UNLOAD_TIME timestamp comment "卸车时间",  --DATE
 CARRIER_ID bigint comment "承运商ID",  --NUMBER(11)
 CARRIER_TYPE smallint comment "承运商类型",  --NUMBER(4)
 CARRIER_NAME string comment "承运商名称",  --NVARCHAR2(50)
 CREATE_BY bigint comment "创建人ID",  --NUMBER(11)
 CREATE_BY_NAME string comment "创建人",  --NVARCHAR2(100)
 CREATE_TIME timestamp comment "创建时间",  --DATE
 UPDATE_BY bigint comment "更新人ID",  --NUMBER(11)
 UPDATE_BY_NAME string comment "更新人",  --NVARCHAR2(100)
 UPDATE_TIME timestamp comment "更新时间"  --DATE
 )     
 COMMENT '运单变化记录表'    
 PARTITIONED BY (dt STRING COMMENT '更新/创建日期 (yyyy-MM-dd)') 
 STORED AS PARQUET   
 LOCATION '/dw/hive/jms_ods.db/external/yl_tms_shipment'    
 TBLPROPERTIES (    
 'discover.partitions'='false',   
 'parquet.column.index.access'='true'   
 );     
     
MSCK REPAIR TABLE jms_ods.yl_tms_shipment;   